package exam.card.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import exam.bank.entity.BankEntity;
import exam.card.dao.CardDao;
import exam.card.entity.CardEntity;
import exam.utils.JDBCUtil;

/**
*@author jzh
*@创建时间 2017年6月17日下午4:02:41
*类说明：
*/
public class CardDaoImpl implements CardDao {

	@Override
	public int addCard(CardEntity entity) throws Exception {
		Connection connection = JDBCUtil.getConnection();
		String sql="insert into card_t (card_name,card_number,card_chip,card_money,card_holder,bank_id) values (?,?,?,?,?,?)";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setString(1, entity.getCardName());
		ps.setString(2, entity.getCardNumber());
		ps.setInt(3, entity.getCardChip());
		ps.setDouble(4, entity.getCardMoney());
		ps.setString(5, entity.getCardHolder());
		ps.setInt(6, entity.getBankId());
		int counter=0;
		counter = ps.executeUpdate();
		return counter;
	}

	@Override
	public List<CardEntity> findAllCard() throws Exception {
		List<CardEntity> cardLists=new ArrayList<CardEntity>();
		Connection connection = JDBCUtil.getConnection();
		String sql="select id as id, card_name as cardName,card_number as cardNumber,card_chip as cardChip,"
				+ "card_money as cardMoney,card_holder as cardHolder,bank_id as bankId from card_t";
		PreparedStatement ps = connection.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			CardEntity cardEntity=new CardEntity();
			
			cardEntity.setId(rs.getInt("id"));
			cardEntity.setCardName(rs.getString("cardName"));
			cardEntity.setCardNumber(rs.getString("cardNumber"));
			cardEntity.setCardChip(rs.getInt("cardChip"));
			cardEntity.setCardMoney(rs.getDouble("cardMoney"));
			cardEntity.setCardHolder(rs.getString("cardHolder"));
			cardEntity.setBankId(rs.getInt("bankId"));
			
			cardLists.add(cardEntity);
		}
		
		return cardLists;
	}

	@Override
	public CardEntity findCardById(int id) throws Exception {
		Connection connection = JDBCUtil.getConnection();
		String sql="select id as id, card_name as cardName,card_number as cardNumber,card_chip as cardChip,"
				+ "card_money as cardMoney,card_holder as cardHolder,bank_id as bankId from card_t where id=?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		CardEntity cardEntity =new CardEntity();
		while(rs.next()){
			cardEntity.setId(rs.getInt("id"));
			cardEntity.setCardName(rs.getString("cardName"));
			cardEntity.setCardNumber(rs.getString("cardNumber"));
			cardEntity.setCardChip(rs.getInt("cardChip"));
			cardEntity.setCardMoney(rs.getDouble("cardMoney"));
			cardEntity.setCardHolder(rs.getString("cardHolder"));
			cardEntity.setBankId(rs.getInt("bankId"));
			
		}
		return cardEntity;
	}

	@Override
	public int updateCard(CardEntity entity) throws Exception {
		Connection connection = JDBCUtil.getConnection();
		String sql="update card_t set card_name=?,card_number=?,card_chip=? ,card_money=?,card_holder=?,bank_id=? where id=?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setString(1, entity.getCardName());
		ps.setString(2, entity.getCardNumber());
		ps.setInt(3, entity.getCardChip());
		ps.setDouble(4, entity.getCardMoney());
		ps.setString(5, entity.getCardHolder());
		ps.setInt(6, entity.getBankId());
		ps.setInt(7,entity.getId());
		int counter = ps.executeUpdate();
		return counter;
	}

	@Override
	public int deleteCard(int id) throws Exception {
		Connection connection = JDBCUtil.getConnection();
		String sql="delete from card_t where id=?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setInt(1, id);
		int counter = ps.executeUpdate();
		return counter;
	}

}
